package com.blacktech.dbu.meta.repository;

import com.blacktech.dbu.meta.entity.DbuFieldLibrary;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

/**
 * Field library data access interface
 * Supports product-level isolation
 *
 * @author Yanyunsong
 */
@Repository
public interface FieldLibraryRepository extends JpaRepository<DbuFieldLibrary, Long>, JpaSpecificationExecutor<DbuFieldLibrary> {

    // ========== Basic query methods ==========

    /**
     * Find field by product ID and English name
     */
    @Query("SELECT f FROM DbuFieldLibrary f WHERE f.productId = :productId AND f.fieldNameEn = :fieldNameEn")
    Optional<DbuFieldLibrary> findByProductAndName(@Param("productId") Long productId, @Param("fieldNameEn") String fieldNameEn);

    
    /**
     * Check if English name exists in product (exclude specified ID)
     */
    @Query("SELECT COUNT(f) > 0 FROM DbuFieldLibrary f WHERE f.productId = :productId AND f.fieldNameEn = :fieldNameEn AND f.id != :id")
    boolean existsByProductAndNameExcludeId(@Param("productId") Long productId, @Param("fieldNameEn") String fieldNameEn, @Param("id") Long id);

    /**
     * Get all enabled fields for specified product
     */
    @Query("SELECT f FROM DbuFieldLibrary f WHERE f.productId = :productId AND f.isEnabled = true ORDER BY f.id ASC")
    List<DbuFieldLibrary> findByProduct(@Param("productId") Long productId);

    // ========== Search and statistics methods ==========

    /**
     * Search fields in product (by Chinese name, English name, description)
     */
    @Query("SELECT f FROM DbuFieldLibrary f WHERE f.productId = :productId AND f.isEnabled = true AND " +
           "(LOWER(f.fieldNameCn) LIKE LOWER(CONCAT('%', :keyword, '%')) OR " +
           "LOWER(f.fieldNameEn) LIKE LOWER(CONCAT('%', :keyword, '%')))")
    Page<DbuFieldLibrary> searchByProduct(@Param("productId") Long productId, @Param("keyword") String keyword, Pageable pageable);

    // ========== Utility methods ==========

    /**
     * Increment field usage count
     */
    @Modifying
    @Query("UPDATE DbuFieldLibrary f SET f.usageCount = f.usageCount + 1 WHERE f.id = :fieldId")
    void incrementUsageCount(@Param("fieldId") Long fieldId);
}